from django.shortcuts import render
from django.http import HttpResponse, HttpRequest, JsonResponse
from .models import *
from django.db.models import Q
import re
from django.utils import timezone
import xlwt
import io
from urllib.parse import quote
from django.db import connection

def index(request: HttpRequest):
    return render(request, 'index.html')

def search(request: HttpRequest):
    page = int(request.GET.get('page', '1'))
    size = int(request.GET.get('size', '5'))
    queryset = Record.objects.filter(is_deleted=False) \
        .defer('is_deleted', 'deleted_time', 'updated_time') \
        .select_related('car')
    carinfo = request.POST.get('carinfo')
    if carinfo:
        carinfo = re.sub(r'\s', '', carinfo)
        queryset = queryset.filter(
            Q(car__carno__startswith=carinfo) | Q(car__owner__contains=carinfo)
        )
    count = queryset.count()
    queryset = queryset.order_by('-makedate')[(page - 1) * size:page * size]
    total_page = (count - 1) // size + 1
    print('page'+str(page))
    print("size"+str(size))
    return render(request, 'index.html', {
        'records': queryset,
        'current_page': page,
        'page_size': size,
        'total_page': total_page,
        'has_prev': page > 1,
        'has_next': page < total_page,
        'prev_page': page - 1,
        'next_page': page + 1,
        'carinfo': carinfo,
    })

def handle(request: HttpRequest):
    try:
        rno = int(request.GET.get('rno'))
        record = Record.objects.filter(no=rno, dealt=False).first()
        if record and not record.dealt:
            record.dealt = True
            record.updated_time = timezone.now()
            record.save()
            data = {'code': 200, 'message': '已受理'}
        else:
            data = {'code': 400, 'message': '不能重复受理'}
    except ValueError:
        data = {'code': 401, 'message': 'invalid car number'}
    return JsonResponse(data)

def delete(request: HttpRequest):
    try:
        rno = int(request.GET.get('rno'))
    except ValueError:
        data = {'code': 401, 'message': 'invalid car number'}
    else:
        record = Record.objects.filter(no=rno, dealt=True).first()
        if record:
            if record.dealt:
                record.is_deleted = True
                record.deleted_time = timezone.now()
                record.save()
                data = {'code': 200, 'message': 'delete successfully'}
            else:
                data = {'code': 400, 'message': 'can not delete which is not dealt'}
        else:
            data = {'code': 402, 'message': 'delete false'}
    return JsonResponse(data)

def export_excel(request: HttpRequest):
    queryset = Record.objects.filter(is_deleted=False)\
        .defer('is_deleted', 'deleted_time', 'updated_time')\
        .select_related('car').order_by('no')
    wb = xlwt.Workbook()
    sheet = wb.add_sheet('违章记录表')
    titles = ('编号', '车牌号', '车主姓名', '违章原因', '违章时间', '处罚方式', '是否受理')
    for col_index, title in enumerate(titles):
        sheet.write(0, col_index, title)
    for row_index, record in enumerate(queryset):
        sheet.write(row_index + 1, 0, record.no)
        sheet.write(row_index + 1, 1, record.car.no)
        sheet.write(row_index + 1, 2, record.car.owner)
        sheet.write(row_index + 1, 3, record.reason)
        sheet.write(row_index + 1, 4, record.makedate.strftime('%Y-%m-%d'))
        sheet.write(row_index + 1, 5, record.punish)
        sheet.write(row_index + 1, 6, '已受理' if record.dealt else '未受理')

    buffer = io.BytesIO()
    wb.save(buffer)
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    filename = quote('违章记录汇总统计.xls')
    # attachment表示以附件的形式进行下载
    # 由于不能直接用单引号 ’ 所以需要做转义处理:\'
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp

def get_bar_data(request: HttpRequest):
    with connection.cursor() as cursor:
        cursor.execute('select carno, ifnull(total, 0) as total from tb_car t1 '
                       ' left outer join (select car_id, count(no) as total from '
                       ' tb_record group by car_id) t2 on t1.no = t2.car_id')

        xdata, ydata = [], []
        for row in cursor.fetchall():
            xdata.append(row[0])
            ydata.append(row[1])
    return JsonResponse({'xdata': xdata, 'ydata': ydata})